package com.infore.exchange.main.utils;

import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.json.JSONUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.util.JdbcUtils;
import com.infore.common.constants.DataStatConstants;
import com.infore.common.exception.MyException;
import com.infore.common.model.entity.exchange.TDatabaseInfo;
import com.infore.common.utils.EncryptAESUtil;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;


@Slf4j
public class SqlUtils {


    /**
     *
     * @param databaseInfo
     * @param jdbcUrl
     * @param dataSourceType 连接池类型
     * @return
     */
    public static DataSource getDataSourceByDatabaseInfo(TDatabaseInfo databaseInfo,String jdbcUrl,String dataSourceType) {
        String dePassword = null;
        try {
            dePassword = EncryptAESUtil.aesDecrypt(databaseInfo.getUserPassword(), DataStatConstants.AES_KEY_PASSWORD);
        } catch (Exception e) {
            throw new MyException("500",databaseInfo.getDatabaseName()+"密码解密失败");
        }
        DataSource dataSource = null;
        if (databaseInfo!=null) {
            String dbType = databaseInfo.getDatabaseType();
            String dbIp = databaseInfo.getDatabaseIp();
            String dbPort = String.valueOf(databaseInfo.getDatabasePort()) ;
            String dbName = databaseInfo.getDatabaseInstanceName();
            String dbUserName = databaseInfo.getUserName();
            String dbUserPassword = dePassword;
            dataSource = getDataSource(dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl, dataSourceType);
        }
        return dataSource;
    }


    /**
     *获取数据源
     * @param dbType
     * @param dbIp
     * @param dbPort
     * @param dbName
     * @param dbUserName
     * @param dbUserPassword
     * @param jdbcUrl
     * @param dataSourceType
     * @return
     */
    public static DataSource getDataSource(String dbType, String dbIp, String dbPort, String dbName, String dbUserName, String dbUserPassword, String jdbcUrl, String dataSourceType) {
        DataSource dataSource = null;
        if ("Druid".equals(dataSourceType)) {
            dataSource = getDruidDataSource(dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl);
        } else if ("Driver".equals(dataSourceType)) {
            dataSource = new DriverManagerDataSource(getJdbcUrl(jdbcUrl, dbType, dbIp, dbPort, dbName), dbUserName, dbUserPassword);
        } else if ("Simple".equals(dataSourceType)) {
            dataSource = getSimpleDataSource(dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl);
        } else if ("Hikari".equals(dataSourceType)) {
            dataSource = getHikariDataSource(dbType, dbIp, dbPort, dbName, dbUserName, dbUserPassword, jdbcUrl);
        }
        return dataSource;
    }


    public static DruidDataSource getDruidDataSource(String dbType, String dbIp, String dbPort, String dbName, String dbUserName, String dbUserPassword, String jdbcUrl) {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(getJdbcUrl(jdbcUrl, dbType, dbIp, dbPort, dbName));
        if ("access".equals(dbType)) {
            dataSource.setDriverClassName("net.ucanaccess.jdbc.UcanaccessDriver");
        } else if ("kingbase".equals(dbType)) {
            dataSource.setDriverClassName("com.kingbase8.Driver");
        }
        dataSource.setUsername(dbUserName);
        dataSource.setPassword(dbUserPassword);
        dataSource.setTestWhileIdle(false);
        dataSource.setFailFast(true);
        return dataSource;
    }


    public static SimpleDataSource getSimpleDataSource(String dbType, String dbIp, String dbPort, String dbName, String dbUserName, String dbUserPassword, String jdbcUrl) {
        jdbcUrl = getJdbcUrl(jdbcUrl, dbType, dbIp, dbPort, dbName);
        String driver = null;
        try {
            if ("access".equals(dbType)) {
                driver = "net.ucanaccess.jdbc.UcanaccessDriver";
            } else if ("kingbase".equals(dbType)) {
                driver = "com.kingbase8.Driver";
            } else {
                driver = JdbcUtils.getDriverClassName(jdbcUrl);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        SimpleDataSource dataSource = new SimpleDataSource(jdbcUrl, dbUserName, dbUserPassword, driver);
        return dataSource;
    }

    public static HikariDataSource getHikariDataSource(String dbType, String dbIp, String dbPort, String dbName, String dbUserName, String dbUserPassword, String jdbcUrl) {
        jdbcUrl =getJdbcUrl(jdbcUrl, dbType, dbIp, dbPort, dbName);
        String driver = null;
        try {
            driver = JdbcUtils.getDriverClassName(jdbcUrl);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(jdbcUrl);
        dataSource.setUsername(dbUserName);
        dataSource.setPassword(dbUserPassword);
        dataSource.setDriverClassName(driver);
        return dataSource;
    }


    /**
     * 拼装Long的where条件
     * @param filterLongKeyColumn
     * @param maxKeyValue
     * @param lastMaxValue
     * @return
     */
    public static String getDataxWhereSql(String filterLongKeyColumn, Long maxKeyValue, Long lastMaxValue) {
        return String.format(" (%s < %s AND %s <= %s) ", maxKeyValue, filterLongKeyColumn, filterLongKeyColumn, lastMaxValue);
    }


    /**
     * 拼装日期类型的where条件
     * @param dbType
     * @param filterTimeColumn
     * @param lastSyncTime
     * @param maxLastupdate
     * @return
     */
    public static String getDataxWhereSql(String dbType, String filterTimeColumn, Timestamp lastSyncTime, Timestamp maxLastupdate) {
        StringBuffer stringBuffer = new StringBuffer(filterTimeColumn);
        if ("sqlserver".equalsIgnoreCase(dbType)) {
            stringBuffer.append(" > CONVERT(datetime,'" + DateFormatUtils.format(lastSyncTime, "yyyy-MM-dd HH:mm:ss.SSS") + "',21) and ");
            stringBuffer.append(filterTimeColumn).append(" <= CONVERT(datetime,'" + DateFormatUtils.format(maxLastupdate, "yyyy-MM-dd HH:mm:ss.SSS") + "',21)");
        } else if ("oracle".equalsIgnoreCase(dbType) || "dm".equalsIgnoreCase(dbType)) {
            stringBuffer.append(" > TO_DATE('" + DateFormatUtils.format(lastSyncTime, "yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss') and ");
            stringBuffer.append(filterTimeColumn).append(" <= TO_DATE('" + DateFormatUtils.format(maxLastupdate, "yyyy-MM-dd HH:mm:ss")+ "','yyyy-MM-dd hh24:mi:ss')");
        } else if ("mysql".equalsIgnoreCase(dbType)) {
            stringBuffer.append(" > str_to_date('" + DateFormatUtils.format(lastSyncTime, "yyyy-MM-dd HH:mm:ss.SSS") + "','%Y-%m-%d %H:%i:%s.%f') and ");
            stringBuffer.append(filterTimeColumn).append(" <= str_to_date('" + DateFormatUtils.format(maxLastupdate, "yyyy-MM-dd HH:mm:ss.SSS") + "','%Y-%m-%d %H:%i:%s.%f')");
        } else if ("postgresq".equalsIgnoreCase(dbType)) {
            stringBuffer.append(" > to_timestamp('" + DateFormatUtils.format(lastSyncTime, "yyyy-MM-dd-HH:mm:ss.SSS") + "','yyyy-MM-dd-hh24:MI:ss.MS') and ");
            stringBuffer.append(filterTimeColumn).append(" <= to_timestamp('" + DateFormatUtils.format(maxLastupdate, "yyyy-MM-dd-HH:mm:ss.SSS") + "','yyyy-MM-dd-hh24:MI:ss.MS')");
        } else {
            stringBuffer.append(" > to_timestamp('" + DateFormatUtils.format(lastSyncTime, "yyyy-MM-dd-HH:mm:ss") + "','yyyy-MM-dd-hh24:MI:ss') and ");
            stringBuffer.append(filterTimeColumn).append(" <= to_timestamp('" + DateFormatUtils.format(maxLastupdate, "yyyy-MM-dd-HH:mm:ss") + "','yyyy-MM-dd-hh24:MI:ss')");
        }
        return stringBuffer.toString();
    }

    /**
     * 获取sql语句中的where条件
     * @param sql sql语句
     * @return where条件
     */
    public static String getWhereCondition(String sql) {
        String whereCondition = "";
        int index = sql.indexOf("where");
        if (index != -1) {
            whereCondition = sql.substring(index + 5);
        }
        return whereCondition;
    }

    /**
     * 动态生成建表语句
     * @param tableName
     * @param tableComment
     * @param fields
     * @return
     */
    public static String createTableSqlGenerate(String tableName,String tableComment, List<Map<String, String>> fields){

        AtomicInteger colNumber = new AtomicInteger();
        // 组装sql语句
        StringBuilder sql = new StringBuilder("create table " + tableName + " ( \n");
        for (Map<String, String> field : fields) {
            colNumber.getAndIncrement();
            sql.append(field.get("name") + " " + field.get("type"));
            if(StringUtils.isNotEmpty(field.get("isnull"))){
                if("false".equals(field.get("isnull"))){
                    sql.append(" not null");
                }
            }
            if(StringUtils.isNotEmpty(field.get("default"))){
                sql.append(" default '"+field.get("default")+"'");
            }
            if(StringUtils.isNotEmpty(field.get("comment"))){
                sql.append(" comment '"+field.get("comment")+"'");
            }
            if (field.get("primary").equals("true")) {
                sql.append(" primary key ");
            }
            if(colNumber.get()!=fields.size()){
                sql.append(", \n");
            }
        }
        sql.append("\n) \n");
        sql.append("comment = '"+tableComment+"'");
        System.out.println(sql);
        return sql.toString();

    }


    /**
     * 获取jdbc连接地址
     */
    public static String getJdbcUrl(String jdbcUrl, String dbType, String dbIp, String dbPort, String dbName) {
        if (StringUtils.isNotEmpty(jdbcUrl)) {
            return jdbcUrl;
        }
        if ("sqlserver".equalsIgnoreCase(dbType)) {
            //jdbc:sqlserver://localhost:3433;DatabaseName=dbname
            jdbcUrl = String.format("jdbc:sqlserver://%s:%s;DatabaseName=%s", dbIp, dbPort, dbName);
        } else if ("oracle".equalsIgnoreCase(dbType)) {
            //jdbc:oracle:thin:@[HOST_NAME]:PORT:[DATABASE_NAME]
            //dbName 为实例名称 例如 ORCL
            jdbcUrl = String.format("jdbc:oracle:thin:@%s:%s/%s", dbIp, dbPort, dbName);
        } else if ("oracleSid".equalsIgnoreCase(dbType)) {
            //jdbc:oracle:thin:@[HOST_NAME]:PORT:[DATABASE_NAME]
            jdbcUrl = String.format("jdbc:oracle:thin:@%s:%s:%s", dbIp, dbPort, dbName);
        } else if ("mysql".equalsIgnoreCase(dbType)) {
            //jdbc:mysql://bad_ip:3306/database
            jdbcUrl = String.format("jdbc:mysql://%s:%s/%s?characterEncoding=utf8&useInformationSchema=true&rewriteBatchedStatements=true", dbIp, dbPort, dbName);
        } else if ("postgresql".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:postgresql://%s:%s/%s", dbIp, dbPort, dbName);
        } else if ("sqlserverold".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:jtds:sqlserver://%s:%s/%s", dbIp, dbPort, dbName);
        } else if ("dm".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:dm://%s:%s/%s", dbIp, dbPort, dbName);
        } else if ("sqlite".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:sqlite:%s", dbName);
        } else if ("h2Embedded".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:h2:%s", dbName);
        } else if ("h2Server".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:h2:tcp://%s:%s/%s", dbIp, dbPort, dbName);
        } else if ("access".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:ucanaccess://%s", dbName);
        } else if ("db2".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:db2://%s:%s/%s", dbIp, dbPort, dbName);
        } else if ("kingbase".equalsIgnoreCase(dbType)) {
            jdbcUrl = String.format("jdbc:kingbase8://%s:%s/%s", dbIp, dbPort, dbName);
        }
//        log.info("解析出jdbcUrl: " + jdbcUrl);
        return jdbcUrl;
    }


    /**
     * 批量插入
     * @param sql
     * @param batchUpdateData
     * @param jdbcTemplate
     * @param dirtyDataNumber
     */
    public static void doBatchInsert(String sql, List<Object[]> batchUpdateData, JdbcTemplate jdbcTemplate, AtomicInteger dirtyDataNumber) {
        DefaultTransactionDefinition transactionDefinition = new DefaultTransactionDefinition();
        DataSourceTransactionManager dm = new DataSourceTransactionManager(jdbcTemplate.getDataSource());
        TransactionStatus tmp = dm.getTransaction(transactionDefinition);
        try {
            jdbcTemplate.batchUpdate(sql, batchUpdateData);
            dm.commit(tmp);
        } catch (Exception e) {
            dm.rollback(tmp);
            log.warn("回滚此次写入, 采用每次写入一行方式提交. 因为:" + e.getMessage());
            for (Object[] batchUpdateDatum : batchUpdateData) {
                try {
                    jdbcTemplate.update(sql, batchUpdateDatum);
                } catch (Exception e1) {
                    log.error(" 脏数据: " + Arrays.toString(batchUpdateDatum), e1.getMessage());
                    dirtyDataNumber.getAndIncrement();
                }
            }
        }
    }

    /**
     * 执行sql
     * @param dataSource
     * @param sql
     * @return
     */
    public static boolean executeSql(DataSource dataSource, String sql) {
        boolean ok = true;
        try {
            JdbcUtils.execute(dataSource, sql);
            log.info("执行sql成功：" + sql);
        } catch (Exception e) {
            log.error("executeSql:" + sql + " 错误：", e);
            log.error("executeSql:" + sql + " 错误：" + e.getMessage());
            ok = false;
        }
        return ok;
    }

    /**
     * 执行查询sql
     * @param dataSource
     * @param sql
     * @return
     */
    public static List<Map<String, Object>> executeQuerySql(DataSource dataSource,  String sql) {
        try {
            List<Map<String, Object>> queryData = JdbcUtils.executeQuery(dataSource, sql);
            log.info("执行QuerySql成功：" + sql);
            return queryData;
        } catch (Exception e) {
            log.error("executeQuerySql:" + sql + " 错误：", e);
            log.error("executeQuerySql:" + sql + " 错误：" + e.getMessage());
            throw new MyException("500","executeQuerySql:" + sql + " 错误：" + e.getMessage());
        }
    }

    /**
    * @Deacription 校验sql
    * @MethodName checkQuerySql
    * @param dataSource
    * @param sql
    * @return {@link boolean}
    * @Author lijianhua
    * @Date 2023/3/2 9:01
    **/
    public static boolean checkQuerySql(DataSource dataSource,  String sql) {
        try {
            List<Map<String, Object>> queryData = JdbcUtils.executeQuery(dataSource, sql);
            log.info("执行QuerySql成功：" + sql);
            return true;
        } catch (Exception e) {
            log.error("executeQuerySql:" + sql + " 错误：", e);
            log.error("executeQuerySql:" + sql + " 错误：" + e.getMessage());
            return false;
        }
    }

    /**
     * 获取表的索引
     * @param dataSource
     * @param catalog
     * @param schema
     * @param table
     * @return
     */
    public static  List<Map<String, Object>> getIndex(DataSource dataSource,String catalog, String schema,String table){

        List<Map<String, Object>> returnList = new ArrayList<>(16);
        //存放列名和结果
        List<Map<String, Object>> values = new ArrayList<>(16);
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            DatabaseMetaData dbmd =conn.getMetaData();
            ResultSet resultSet = dbmd.getIndexInfo(catalog, schema, table, false, false);
            //处理 ResultSet
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            //迭代
            while (resultSet.next()) {
                //map object
                HashMap<String, Object> value = new HashMap<>(16);
                //循环所有的列，获取列名，根据列名获取值
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    value.put(columnName, resultSet.getString(i));
                }
                //add object
                values.add(value);
            }
            System.out.println(JSONUtil.toJsonStr(values));

            returnList = values.stream().filter(f->
                f.get("COLUMN_NAME")!=null && f.get("COLUMN_NAME").toString()!=""
            ).collect(Collectors.toList());

            System.out.println(JSONUtil.toJsonStr(returnList));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn);
        }
        return returnList;
    }


    /**
    * @Deacription 构建MySQL数据库保存或更新SQL语句，即处理主键冲突，存在则更新
    * @MethodName buildMysqlSaveOrUpdateSql
    * @param tableName
    * @param targetFields
    * @return {@link StringBuilder}
    * @Author lijianhua
    * @Date 2023/3/2 9:45
    **/
    public static StringBuilder buildMysqlSaveOrUpdateSql(String tableName,String[] targetFields) {
        StringBuilder sb = new StringBuilder(String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, String.join(",", targetFields),
                StringUtils.repeat("?", ",", targetFields.length))) ;
        sb.append(" ON DUPLICATE KEY UPDATE ");
        for(int i = 0; i< targetFields.length; i++) {
            sb.append(targetFields[i] + "= ?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb;
    }

    /**
    * @Deacription 构建Oracle数据库保存或更新SQL语句，即处理主键冲突，存在则更新
    * @MethodName buildOracleSaveOrUpdateSql
    * @param tableName
    * @param primaryKey
    * @param targetFields
    * @return {@link StringBuilder}
    * @Author lijianhua
    * @Date 2023/3/2 10:18
    **/
    public static StringBuilder buildOracleSaveOrUpdateSql(String tableName,String primaryKey,String[] targetFields) {
        StringBuilder sb = new StringBuilder(String.format("merge into  %s t using dual on (%s  = ?) " +
                        "when not matched then insert  (%s) VALUES (%s)", tableName,primaryKey, String.join(",", targetFields),
                StringUtils.repeat("?", ",", targetFields.length))) ;
        sb.append("WHEN MATCHED THEN update " + " set ");
        for (int i = 0; i < targetFields.length; i++) {
            if((targetFields[i].toUpperCase()).equals(primaryKey)) {
                continue;
            }
            sb.append(targetFields[i] + "= ?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb;
    }

}
